package com.mvc.cms.dao.assist.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.mvc.cms.dao.assist.CmsHouseDataDao;
import com.mvc.cms.entity.assist.CmsHouseData;
import com.mvc.common.hibernate3.Finder;
import com.mvc.common.hibernate3.HibernateBaseDao;
import com.mvc.common.page.Pagination;
@Repository
public class CmsHouseDataDaoImpl extends 
                   HibernateBaseDao<CmsHouseData, Integer> implements CmsHouseDataDao{

	@Override
	protected Class<CmsHouseData> getEntityClass() {
		return CmsHouseData.class;
	}
	
	public CmsHouseData save(CmsHouseData bean) {
		getSession().save(bean);
		return bean;
	}

	public CmsHouseData find(Integer id) {
		return get(id);
	}

	public CmsHouseData delete(Integer id) {
		CmsHouseData bean = get(id);
		if(bean != null){
			getSession().delete(bean);
		}
		return bean;
	}
	
	public Pagination getPage(String name, String area, String seller,
			String license, String use, String building, Date begin, Date end,
			int pageNo, int pageSize) {
		Finder f = Finder.create(" from CmsHouseData bean where 1=1");
		if(!StringUtils.isBlank(name)){
			f.append(" and bean.name like '%"+name+"%'");
		}
		if(!StringUtils.isBlank(area)){
			f.append(" and bean.area=:area");
			f.setParam("area", area);
		}
		if(!StringUtils.isBlank(seller)){
			f.append(" and bean.seller like '%"+seller+"%'");
		}
		if(!StringUtils.isBlank(license)){
			f.append(" and bean.license=:license");
			f.setParam("license", license);
		}
		if(!StringUtils.isBlank(use)){
			f.append(" and bean.use=:use");
			f.setParam("use", use);
		}
		if(!StringUtils.isBlank(building)){
			f.append(" and bean.building=:building");
			f.setParam("building", building);
		}
		if(begin != null || end !=null){
			f.append(" and bean.contractTime >= :begin");
			f.append(" and bean.contractTime <= :end");
			f.setParam("begin", begin);
			f.setParam("end", end);
		}
		f.append(" order by bean.id desc");
		return find(f, pageNo, pageSize);
	}

	private Finder createCacheableFinder(String hql) {
		Finder finder = Finder.create(hql);
		finder.setCacheable(true);
		return finder;
	}
	
	public List<Object[]> rangeStatistics(String name,String area,String seller,
	        String license,String use,String building,Date begin,Date end) {
		Finder f = createCacheableFinder("select bean.area,count(bean.id),sum(bean.turnover),sum(bean.covered) " +
		   ",sum(bean.sets) from CmsHouseData bean where 1=1");
		if(!StringUtils.isBlank(name)){
			f.append(" and bean.name like '%"+name+"%'");
		}
		if(!StringUtils.isBlank(area)){
			f.append(" and bean.area=:area");
			f.setParam("area", area);
		}
		if(!StringUtils.isBlank(seller)){
			f.append(" and bean.seller like '%"+seller+"%'");
		}
		if(!StringUtils.isBlank(license)){
			f.append(" and bean.license=:license");
			f.setParam("license", license);
		}
		if(!StringUtils.isBlank(use)){
			f.append(" and bean.use=:use");
			f.setParam("use", use);
		}
		if(!StringUtils.isBlank(building)){
			f.append(" and bean.building=:building");
			f.setParam("building", building);
		}
		if (begin !=null && end != null) {
			f.append(" and bean.contractTime >= :begin");
			f.append(" and bean.contractTime < :end");
			f.setParam("begin", begin);
			f.setParam("end", end);
		}
		f.append(" group by bean.area");
		return find(f);
	}

	public List<Object[]> propertyStatistics(String name,String area,String seller,
	        String license,String use,String building,Date begin, Date end) {
		Finder f = createCacheableFinder("select bean.name,bean.seller,bean.area,count(bean.id),sum(bean.turnover)," +
		 "sum(bean.covered),sum(bean.sets) from CmsHouseData bean where 1=1");
		if(!StringUtils.isBlank(name)){
			f.append(" and bean.name like '%"+name+"%'");
		}
		if(!StringUtils.isBlank(area)){
			f.append(" and bean.area=:area");
			f.setParam("area", area);
		}
		if(!StringUtils.isBlank(seller)){
			f.append(" and bean.seller like '%"+seller+"%'");
		}
		if(!StringUtils.isBlank(license)){
			f.append(" and bean.license=:license");
			f.setParam("license", license);
		}
		if(!StringUtils.isBlank(use)){
			f.append(" and bean.use=:use");
			f.setParam("use", use);
		}
		if(!StringUtils.isBlank(building)){
			f.append(" and bean.building=:building");
			f.setParam("building", building);
		}
		if (begin !=null && end != null) {
			f.append(" and bean.contractTime >= :begin");
			f.append(" and bean.contractTime < :end");
			f.setParam("begin", begin);
			f.setParam("end", end);
		}
		f.append(" group by bean.name");
		return find(f);
	}
	
	@Override
	public List<String> getAllRow(String key) {
	
		StringBuffer sb=new StringBuffer("select distinct "+key+" from DataDictionary");
		List<String> list=new ArrayList();
		list=super.find(sb.toString(), null);
		return list;
	}
}
